package bdd;

import beans.*;
import java.sql.*;
import java.util.ArrayList;

/**
 * Appel des queries pour : - Gérer les paramètres 'transversaux' de
 * l'application (Taux TVA, frais de port et liste des types d'adresses &
 * téléphones) - Gérer les profils des vendeurs
 *
 * @author Loic Jacolin-Lescot
 */
public class QueriesOptions {

    public Connection con;

    public QueriesOptions() throws ClassNotFoundException, SQLException {
        con = ConnectBDD.getSingleton();
    }

    /*Requêtes profils vendeurs*/
    public ArrayList<OptVendeurs> listerVendeurs() throws SQLException {
        ArrayList<OptVendeurs> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerVendeur}");
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            OptVendeurs ven = new OptVendeurs();
            ven.setVenActif(res.getBoolean("VEN_ACTIF"));
            ven.setVenId(res.getInt("VEN_ID"));
            ven.setVenMailPro(res.getString("VEN_MAILPRO"));
            ven.setVenPseudo(res.getString("VEN_PSEUDO"));
            ven.setVenPwd(res.getString("VEN_PWD"));
            liste.add(ven);
        }
        ConnectBDD.fermer();
        return liste;
    }

    public ArrayList<OptVendeurs> listerVendeursActif() throws SQLException {
        ArrayList<OptVendeurs> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerVendeurActif}");
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            OptVendeurs ven = new OptVendeurs();
            ven.setVenActif(res.getBoolean("VEN_ACTIF"));
            ven.setVenId(res.getInt("VEN_ID"));
            ven.setVenMailPro(res.getString("VEN_MAILPRO"));
            ven.setVenPseudo(res.getString("VEN_PSEUDO"));
            ven.setVenPwd(res.getString("VEN_PWD"));
            liste.add(ven);
        }
        ConnectBDD.fermer();
        return liste;
    }

    public int modifierVendeurs(OptVendeurs ven) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_modifierVendeur(?,?,?,?,?)}");

        cstmt.setInt(1, ven.getVenId());
        cstmt.setString(2, ven.getVenPseudo());
        cstmt.setString(3, ven.getVenMailPro());
        cstmt.setBoolean(4, ven.isVenActif());
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(5);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int desactVendeurs(OptVendeurs ven) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_desactVendeur(?,?,?)}");

        cstmt.setBoolean(1, ven.isVenActif());
        cstmt.setInt(2, ven.getVenId());
        cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(3);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int modifierPwdVendeurs(OptVendeurs ven) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_modifierPwdVendeur(?,?,?)}");

        cstmt.setInt(2, ven.getVenId());
        cstmt.setString(1, ven.getVenPwd());
        cstmt.registerOutParameter(3, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(3);

        bdd.ConnectBDD.fermer();
        return i;

    }

    public int creerVendeurs(OptVendeurs ven) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_creerVendeur(?,?,?,?,?)}");

        cstmt.setString(1, ven.getVenPseudo());
        cstmt.setString(2, ven.getVenMailPro());
        cstmt.setString(3, ven.getVenPwd());
        cstmt.setBoolean(4, ven.isVenActif());
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(5);

        bdd.ConnectBDD.fermer();
        return i;
    }

    /*Requêtes gstion des paramètres */
    public ArrayList<OptTva> listerTva() throws SQLException {
        ArrayList<OptTva> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerTva}");
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            OptTva tva = new OptTva();
            tva.setTvaID(res.getInt("TVA_ID"));
            tva.setTvaLabel(res.getString("TVA_LABEL"));
            tva.setTvaTaux(res.getFloat("TVA_TAUX"));
            tva.setTvaDeb(res.getString("TVA_DEB"));
            tva.setTvaFin(res.getString("TVA_FIN"));
            liste.add(tva);
        }
        ConnectBDD.fermer();
        return liste;
    }

    public int ajouterTva(OptTva tva) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_ajouterTva(?,?,?,?,?)}");

        cstmt.setString(1, tva.getTvaLabel());
        cstmt.setFloat(2, tva.getTvaTaux());
        cstmt.setString(3, tva.getTvaDeb());
        cstmt.setString(4, tva.getTvaFin());
        cstmt.registerOutParameter(5, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(5);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public ArrayList<OptTypeEnvois> listerTypeEnvois() throws SQLException {
        ArrayList<OptTypeEnvois> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerTypeEnvois}");
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            OptTypeEnvois env = new OptTypeEnvois();
            env.setTenID(res.getInt("TEN_ID"));
            env.setTenLabel(res.getString("TEN_LABEL"));
            env.setTenPrix(res.getFloat("TEN_PRIX"));
            env.setTenDelai(res.getString("TEN_DELAI"));
            liste.add(env);
        }
        ConnectBDD.fermer();
        return liste;
    }

    public int ajouterTypeEnvois(OptTypeEnvois env) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_ajouterTypeEnvois(?,?,?,?)}");

        cstmt.setString(1, env.getTenLabel());
        cstmt.setFloat(2, env.getTenPrix());
        cstmt.setString(3, env.getTenDelai());
        cstmt.registerOutParameter(4, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(4);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public ArrayList<OptTypeAdr> listerTypeAdresses() throws SQLException {
        ArrayList<OptTypeAdr> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerTypeAdr}");
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            OptTypeAdr tad = new OptTypeAdr();
            tad.setTadId(res.getInt("TAD_ID"));
            tad.setTadLabel(res.getString("TAD_LABEL"));
            liste.add(tad);
        }
        ConnectBDD.fermer();
        return liste;
    }

    public ArrayList<OptTypePhones> listerTypePhones() throws SQLException {
        ArrayList<OptTypePhones> liste = new ArrayList();

        CallableStatement cstmt = con.prepareCall("{call ps_listerTypePhones}");
        ResultSet res = cstmt.executeQuery();

        while (res.next()) {
            OptTypePhones ttp = new OptTypePhones();
            ttp.setTtpId(res.getInt("TTP_ID"));
            ttp.setTtpLabel(res.getString("TTP_LABEL"));
            liste.add(ttp);
        }
        ConnectBDD.fermer();
        return liste;
    }

    public int ajouterTypeAdresses(OptTypeAdr tad) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_ajouterTypeAdresses(?,?)}");

        cstmt.setString(1, tad.getTadLabel());
        cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(2);

        bdd.ConnectBDD.fermer();
        return i;
    }

    public int ajouterTypePhones(OptTypePhones ttp) throws SQLException {
        CallableStatement cstmt = con.prepareCall("{call ps_ajouterTypePhones(?,?)}");

        cstmt.setString(1, ttp.getTtpLabel());
        cstmt.registerOutParameter(2, java.sql.Types.INTEGER);
        cstmt.execute();
        int i = cstmt.getInt(2);

        bdd.ConnectBDD.fermer();
        return i;
    }
}
